#!/usr/bin/python3
# -*- coding: utf-8 -*-
# ======================================#
# @Author   : duanqizhong
# @time     : 2022-8-16 11:26
# @file     : get_slow_sql
# @Software : PyCharm
# ======================================#

import datetime
import sys

from base.cls import SSHClient
from base.func import slow_log_path
from base.logger import log
from settings import DB_Conf, RSA_KEY_PATH, DB_Anemometer


def collect_slowQuery(env, conn):
    slow_log = slow_log_path(conn)
    if not slow_log:
        log.error("未获取到数据库慢查日志位置，可能数据库已关闭或者慢查未开启！")
        return
    if DB_Conf.get(env) and not DB_Conf.get(env).get("slow_log"):
        log.warn(f"{env} slow_log=False!")
        return
    slow_query_file = slow_log.get("value")
    ssh_client = SSHClient(conn["host"], RSA_KEY_PATH)

    today = datetime.datetime.now()
    offset = datetime.timedelta(days=-1)
    time_format = "%Y-%m-%d 00:00:00"
    start_time = (today + offset).strftime(time_format)
    end_time = today.strftime(time_format)

    review_table(ssh_client, slow_query_file, start_time, end_time)
    history_table(ssh_client, slow_query_file, start_time, end_time)


def review_table(ssh_client, slow_query_file, start_time, end_time):
    tools = "pt-query-digest"
    conn = f"--user={DB_Anemometer['username']} --password={DB_Anemometer['password']}"
    review_table = f"--review h={DB_Anemometer['host']},P={DB_Anemometer['port']},D={DB_Anemometer['database']},t={DB_Anemometer['query_review_table']}"
    params = "--no-report --limit=0%"
    start_end = '''--since "{start_time}" --until "{end_time}"'''.format(start_time=start_time, end_time=end_time)
    filters = """--filter '($event->{user} || "") =~ m/^vipkid/i and $event->{host} =~ m/^10\./i'"""

    cmd = """{tools} {conn} {review} {params} {filters} {start_end} {slow_query_file}""".format(tools=tools, conn=conn,
                                                                                                review=review_table,
                                                                                                filters=filters,
                                                                                                params=params,
                                                                                                start_end=start_end,
                                                                                                slow_query_file=slow_query_file)
    log.info(cmd)
    ssh_client.ssh_cmd(cmd)



def history_table(ssh_client, slow_query_file, start_time, end_time):
    tools = "pt-query-digest"
    conn = f"--user={DB_Anemometer['username']} --password={DB_Anemometer['password']}"
    history_table = f"--review h={DB_Anemometer['host']},P={DB_Anemometer['port']},D={DB_Anemometer['database']},t={DB_Anemometer['query_review_history_table']}"

    params = "--no-report --limit=0%"
    start_end = '''--since "{start_time}" --until "{end_time}"'''.format(start_time=start_time, end_time=end_time)
    # filters="""--filter '($event->{user} || "") =~ m/^vipkid/i and $event->{host} =~ m/^10./i'"""
    filters_arg = '''--filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\\\"%s:%s:%s\\\""''' % (
        env, DB_Conf[env]["host"], DB_Conf[env]["port"])

    # cmd="""{tools} {conn} {history} {params} {filters} {filters_arg} {start_end} {slow_query_file}""".format(tools=tools,conn=conn,history=history_table,params=params,filters=filters,filters_arg=filters_arg,start_end=start_end,slow_query_file=slow_query_file)
    cmd = """{tools} {conn} {history} {params} {filters_arg} {start_end} {slow_query_file}""".format(tools=tools,
                                                                                                     conn=conn,
                                                                                                     history=history_table,
                                                                                                     params=params,
                                                                                                     filters_arg=filters_arg,
                                                                                                     start_end=start_end,
                                                                                                     slow_query_file=slow_query_file)
    ssh_client.ssh_cmd(cmd)


def run(env=None):
    if env is None:
        for key, value in DB_Conf.items():
            conn = value
            # conn.update(mescanal)
            collect_slowQuery(key, conn)
    else:
        if env in DB_Conf.keys():
            conn = DB_Conf[env]
            # conn.update(mescanal)
            collect_slowQuery(env, conn)
        else:
            log.error("输入的环境未在配置文件内，请核实后继续尝试！")
            return


if __name__ == "__main__":
    lens = len(sys.argv)
    if sys.argv[-1].lower() != 'yes' and lens not in (2, 3):
        print("参数格式：yes or env yes")
        sys.exit()
    if lens == 2:
        log.info("收集所有配置内的慢sql")
        run()
    else:
        env = sys.argv[1]
        log.info("收集{env}环境的慢查".format(env=env))
        run(env)
